﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Web.Hosting;
using _4T.Data;
using System.Globalization;

namespace _4T.DAL
{
    public class ProfileStageLinkDAL
    {
        public ProfileStageLinkDAL() { }
        public int InsertProfileStageLink(int profileStatusID, int stage, string video, string audio, string photo, DateTime? editTime = null)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileStageLinkInsert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileStatusID", SqlDbType.Int).Value = profileStatusID;
                    cmd.Parameters.Add("@Stage", SqlDbType.Int).Value = stage;
                    cmd.Parameters.Add("@VideoLink", SqlDbType.NText).Value = video;
                    cmd.Parameters.Add("@AudioLink", SqlDbType.NText).Value = audio;
                    cmd.Parameters.Add("@PhotoLink", SqlDbType.NText).Value = photo;
                    cmd.Parameters.Add("@LastEditTime", SqlDbType.DateTime).Value = editTime;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public int DeleteProfile(string personalCode, string profileCode)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "sp_DeleteProfile";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@profileCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Parameters.Add("@PersonalCode", SqlDbType.VarChar, 50).Value = personalCode;
                    cmd.Connection = conn;
                    result = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {

                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
            return result;
        }

        public List<ProfileStageLink> GetListProfileStatusLink(string profileCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileStageLinkGetList";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("ProfileInfoCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Connection = conn;
                    reader = cmd.ExecuteReader();
                    if (reader != null)
                    {
                        List<ProfileStageLink> list = new List<ProfileStageLink>();
                        while (reader.Read())
                        {
                            ProfileStageLink item = GetProfileStageLinkFromReader(reader);
                            list.Add(item);
                        }
                        return list;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return null;
        }

        public ProfileStageLink GetProfileStageLinkByProfileCodeAndStage(string profileCode, int stage)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;
                ProfileStageLink link = null;
                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileStageLinkGet";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileInfoCode", SqlDbType.VarChar, 50).Value = profileCode;
                    cmd.Parameters.Add("@Stage", SqlDbType.Int).Value = stage;
                    cmd.Connection = conn;

                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        link = GetProfileStageLinkFromReader(reader);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }

                return link;
            }

            return null;
        }

        private ProfileStageLink GetProfileStageLinkFromReader(SqlDataReader reader)
        {
            string virtualPath = HostingEnvironment.SiteName + HostingEnvironment.ApplicationVirtualPath + "/";
            if (HostingEnvironment.ApplicationVirtualPath == "/")
            {
                virtualPath = HostingEnvironment.SiteName + HostingEnvironment.ApplicationVirtualPath;
            }
            string rootUrl = ConfigurationManager.AppSettings["rootUrl"];

            int stage = 0;
            if (reader.GetValue(0) != DBNull.Value)
                stage = reader.GetInt32(0);

            string video = string.Empty;
            if (reader.GetValue(1) != DBNull.Value)
                video = reader.GetString(1);//virtualPath + reader.GetString(1);
            if (!string.IsNullOrEmpty(video)) video = rootUrl + video;

            string audio = string.Empty;
            if (reader.GetValue(2) != DBNull.Value)
                audio = reader.GetString(2); //virtualPath + reader.GetString(2);
            if (!string.IsNullOrEmpty(audio)) audio = rootUrl + audio;

            string photo = string.Empty;
            if (reader.GetValue(3) != DBNull.Value)
                photo = reader.GetString(3); //virtualPath + reader.GetString(3);
            if (!string.IsNullOrEmpty(photo)) photo = rootUrl + photo;

            string description = string.Empty;
            if (reader.GetValue(4) != DBNull.Value)
                description = reader.GetString(4);

            string editTime = string.Empty;
            if (reader.GetValue(5) != DBNull.Value)
                editTime = reader.GetDateTime(5).ToString("G", CultureInfo.CreateSpecificCulture("vi-VI"));

            return new ProfileStageLink(stage, video, audio, photo, description, editTime);
        }

        public int InsertProfileStageLink(string profileInfoCode, int stage, string video, string audio, string photo, string description, DateTime editTime)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "ProfileStageLinkInsertWithProfileInfoCode";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProfileInfoCode", SqlDbType.VarChar, 50).Value = profileInfoCode;
                    cmd.Parameters.Add("@Stage", SqlDbType.Int).Value = stage;
                    cmd.Parameters.Add("@VideoLink", SqlDbType.NText).Value = video;
                    cmd.Parameters.Add("@AudioLink", SqlDbType.NText).Value = audio;
                    cmd.Parameters.Add("@PhotoLink", SqlDbType.NText).Value = photo;
                    cmd.Parameters.Add("@Description", SqlDbType.NText).Value = description;
                    cmd.Parameters.Add("@LastEditTime", SqlDbType.DateTime).Value = editTime;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }
    }
}